package net.zjitc.jdbc.book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @Author admin
 * @Date 2019/4/26 8:15
 * 图书管理的工具类，用JDBC实现
 * 完成图书的增删改查操作
 */
public class BookDaoDB implements BookDao{
    Connection conn=JDBCUtils.getConnection();
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    /**
     * 添加功能：将book对象保存在DB
     * 要求：图书名称不能重复
     * @param book
     * @return
     */
    @Override
    public int insert(Book book) {
        /* 首先判断DB中是否已经包含该图书名称
        *  若已包含，这返回0，表示图书名称重复了
        *  若不包含，将book添加到DB:
        *      获取数据库连接
         *     获取执行SQL的pstmt对象
         *     设置参数值
         *     执行SQL */
        Book b=query(book.getName());
        if(b==null){
            String sql="insert into book(name,press,author,price) values(?,?,?,?)";
            try {
                pstmt=conn.prepareStatement(sql);
                pstmt.setString(1,book.getName());
                pstmt.setString(2,book.getPress());
                pstmt.setString(3,book.getAuthor());
                pstmt.setDouble(4,book.getPrice());
                int c=pstmt.executeUpdate();
                return c;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 根据name查询数据库中是否存在该对象，若不存在null
     * @param name
     * @return
     */
    @Override
    public Book query(String name) {
        String sql="select * from book where name=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            rs=pstmt.executeQuery();
            /* 因为图书名称不能重复，所以结果集中只查询出一条语句 */
            if (rs.next()){
               /* 结果集中的内容 返回*/
                int id=rs.getInt("id");
                String bookName=rs.getString("name");
                String press=rs.getString("press");
                String author=rs.getString("author");
                double price=rs.getDouble("price");
                Book book=new Book(id,name,press,author,price);
                return book;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询功能：查询出一条语句就显示出来
     * 这种方式不可取，内容的展示应该放在测试类去实现
     */
    @Override
    public void select() {
        String sql = "select * from book";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                System.out.println(id+"\t"+name+"\t"+press+"\t"+author+"\t"+price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 查询功能：查询出所有记录，添加到集合中 ，并返回集合
     * 这种方式最可取。内容的展示应该让测试类来做
     * @return
     */
    @Override
    public ArrayList<Book> selectAll() {
        ArrayList<Book> arrayList=new ArrayList<Book>();
        String sql = "select * from book";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                Book book=new Book(id,name,press,author,price);
                arrayList.add(book);
            }
            return arrayList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int update(Book b) {
        String sql="update book set name=?,press=?,author=?,price=? where id=?";
        try {
            pstmt=conn.prepareStatement(sql);
            /* 设置5个？的值*/
            pstmt.setString(1,b.getName());
            pstmt.setString(2,b.getPress());
            pstmt.setString(3,b.getAuthor());
            pstmt.setDouble(4,b.getPrice());
            pstmt.setInt(5,b.getId());
            /* 执行修改的SQL语句，并返回影响了多少行*/
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public void exit() {
        JDBCUtils.close(conn,pstmt,rs);
    }

    @Override
    public int delete(Book book) {
        String sql="delete from book where id=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,book.getId());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
